﻿Imports System.Data.SqlClient
Public Class frmXuatKho
    Dim themmoi As Boolean = False
    Dim themmoict As Boolean = True
    Private strMaPhieu As String = ""
    Public booThanhCong As Boolean = False
    Dim aryDelRow As New ArrayList

    Sub New(Optional ByVal MaPhieu As String = "")

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        strMaPhieu = MaPhieu
    End Sub

    Private Sub frmXuatKho_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.dtpNgayCT.Value = Now
        Me.dtpNgayHD.Value = Now
        updateComboTaiKhoan()
        updateComboKhachHang()
        updateComboHangHoa()
        dgvList.DataSource = dtsData.Tables(0)
        Me.dgvList.AutoGenerateColumns = False
        If strMaPhieu <> "" Then
            If strMaPhieu = -1 Then
                DocDuLieu(strMaPhieu, 4)
            Else
                DocDuLieu(strMaPhieu)
            End If
        End If
    End Sub

    Private Sub DocDuLieu(ByVal MaPhieu As String, Optional ByVal kind As Integer = 0)
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myComm As New SqlCommand
            myComm.Connection = myConn
            Select Case kind
                Case 0
                    strSql &= "Select * from SoCai where MaPhieu = @MaPhieu"
                    myComm.Parameters.AddWithValue("@MaPhieu", MaPhieu)
                Case 1
                    strSql &= "Select Top 1 * from SoCai where LoaiCT = 'PXK' order by NgayCT asc"
                Case 2
                    strSql &= "Select Top 1 * from SoCai where NgayCT <= convert(DATETIME, @NgayCT, 103) and MaPhieu <> @MaPhieu and LoaiCT = 'PXK' order by NgayCT desc"
                    myComm.Parameters.AddWithValue("@NgayCT", dtpNgayCT.Value.ToString("dd/MM/yyyy"))
                    myComm.Parameters.AddWithValue("@MaPhieu", MaPhieu)
                Case 3
                    strSql &= "Select Top 1 * from SoCai where NgayCT >= convert(DATETIME, @NgayCT, 103) and MaPhieu <> @MaPhieu and LoaiCT = 'PXK' order by NgayCT asc"
                    myComm.Parameters.AddWithValue("@NgayCT", dtpNgayCT.Value.ToString("dd/MM/yyyy"))
                    myComm.Parameters.AddWithValue("@MaPhieu", MaPhieu)
                Case 4
                    strSql &= "Select Top 1 * from SoCai where LoaiCT = 'PXK' order by NgayCT desc"
            End Select
            myComm.CommandText = strSql
            Dim myAdapter As New SqlDataAdapter(myComm)
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                dtsData.Tables(0).Rows.Clear()
                strMaPhieu = myTable.Rows(0).Item("MaPhieu")
                Me.txtSoCT.Text = myTable.Rows(0).Item("SoCT")
                Me.txtSoHD.Text = myTable.Rows(0).Item("SoHD")
                Me.dtpNgayCT.Value = myTable.Rows(0).Item("NgayCT")
                Me.dtpNgayHD.Value = myTable.Rows(0).Item("NgayHD")
                Me.txtDienGiai.Text = myTable.Rows(0).Item("GhiChu")
                Me.cbxTKCo.SelectedValue = myTable.Rows(0).Item("MaTK_Co")
                Me.cbxTKNo.SelectedValue = myTable.Rows(0).Item("MaTK_No")
                Me.cbxTKThue.SelectedValue = myTable.Rows(0).Item("TKThue")
                Me.cbxKhachHang.SelectedValue = myTable.Rows(0).Item("MaKH")
                Me.txtTongTien.Text = formatNumber(myTable.Rows(0).Item("TongTien").ToString())
                Me.txtTongThue.Text = formatNumber(myTable.Rows(0).Item("TongThue").ToString())
                Me.txtTruocThue.Text = formatNumber(myTable.Rows(0).Item("TruocThue").ToString())
                DocDuLieuCT(myTable.Rows(0).Item("MaPhieu"))
            ElseIf kind = 4 Then
                ThemMoiDuLieu()
            End If
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Sub DocDuLieuCT(ByVal MaPhieu As String)
        Dim strSql As String = ""
        strSql &= "Select "
        strSql &= " MaPhieuCT, "
        strSql &= " ROW_NUMBER() OVER (ORDER BY ct.MaPhieuCT) as STT, "
        strSql &= " ct.MaHH, "
        strSql &= " ct.SoLuong, "
        strSql &= " dv.TenDV as DVT, "
        strSql &= "	ct.DonGia, "
        strSql &= " ct.SoLuong * ct.DonGia as ThanhTien, "
        strSql &= "	ct.ThueSuat, "
        strSql &= "	ct.TienThue, "
        strSql &= "	ct.TienThue + ct.SoLuong * ct.DonGia as TongTien, "
        strSql &= "	hh.TenHH "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " inner join HangHoa hh on hh.MaHH = ct.MaHH"
        strSql &= " left join DonVi dv on dv.MaDV = hh.MaDV"
        strSql &= " where"
        strSql &= " sc.MaPhieu = " & MaPhieu
        strSql &= " order by MaPhieuCT"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myComm As New SqlCommand
            myComm.Connection = myConn
            myComm.CommandText = strSql
            Dim myAdapter As New SqlDataAdapter(myComm)
            myAdapter.Fill(dtsData.Tables(0))
            Dim index As Integer = 0
            For Each row As DataRow In dtsData.Tables(0).Rows
                dgvList.Rows(index).Tag = row(2)
                row(3) = formatNumber(row(3).ToString())
                row(5) = formatNumber(row(5).ToString())
                row(6) = formatNumber(row(6).ToString())
                row(7) = formatNumber(row(7).ToString())
                row(8) = formatNumber(row(8).ToString())
                row(9) = formatNumber(row(9).ToString())
                index += 1
            Next
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Sub txtTongTienHang_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTongTienHang.KeyDown
        If e.KeyCode = Keys.Enter Then
            If checkValidDetail() Then
                If themmoict Then
                    Me.cbxHangHoa.Focus()
                    Dim row As DataRow = dtsData.Tables(0).NewRow
                    row(0) = ""
                    row(1) = dtsData.Tables(0).Rows.Count + 1
                    row(2) = Me.cbxHangHoa.SelectedValue
                    row(3) = Me.txtSL.Text
                    row(4) = Me.txtDVT.Text
                    row(5) = Me.txtDonGia.Text
                    row(6) = Me.txtThanhTien.Text
                    row(7) = Me.txtThueSuat.Text
                    row(8) = Me.txtTienThue.Text
                    row(9) = Me.txtTongTienHang.Text
                    row(10) = cbxHangHoa.Text
                    row(3) = formatNumber(row(3))
                    row(5) = formatNumber(row(5))
                    row(6) = formatNumber(row(6))
                    row(7) = formatNumber(row(7))
                    row(8) = formatNumber(row(8))
                    row(9) = formatNumber(row(9))
                    dtsData.Tables(0).Rows.Add(row)
                    clearDetail()
                Else
                    Me.cbxHangHoa.Focus()
                    Dim row As DataRow = dtsData.Tables(0).Rows(dgvList.CurrentRow.Index)
                    row(2) = Me.cbxHangHoa.SelectedValue
                    row(3) = Me.txtSL.Text
                    row(4) = Me.txtDVT.Text
                    row(5) = Me.txtDonGia.Text
                    row(6) = Me.txtThanhTien.Text
                    row(7) = Me.txtThueSuat.Text
                    row(8) = Me.txtTienThue.Text
                    row(9) = Me.txtTongTienHang.Text
                    row(10) = cbxHangHoa.Text
                    row(3) = formatNumber(row(3))
                    row(5) = formatNumber(row(5))
                    row(6) = formatNumber(row(6))
                    row(7) = formatNumber(row(7))
                    row(8) = formatNumber(row(8))
                    row(9) = formatNumber(row(9))
                    MsgBox("Cập nhật vào danh sách thành công.")
                    clearDetail()
                End If
                Dim tongtien As Double = 0
                Dim truocthue As Double = 0
                Dim tongthue As Double = 0
                For Each row As DataRow In dtsData.Tables(0).Rows
                    truocthue += CDbl(row(6))
                    tongthue += convertNumber(row(8))
                    tongtien += CDbl(row(9))
                Next
                txtTruocThue.Text = truocthue.ToString("#,###")
                txtTongThue.Text = tongthue.ToString("#,###")
                txtTongTien.Text = tongtien.ToString("#,###")
                themmoict = True
            End If
        End If
    End Sub

    Private Sub frmXuatKho_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles MyBase.KeyDown
        If e.KeyCode = Keys.Enter Then
            If Not Me.ActiveControl Is Nothing AndAlso (Me.ActiveControl.Name <> "txtDienGiai" And Me.ActiveControl.Name <> "txtTongTienHang" And Me.ActiveControl.Name.IndexOf("btn") = -1) Then
                SendKeys.Send("{Tab}")
                e.Handled = True
            End If
        End If
    End Sub

    Private Sub cbxTKNo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTKNo.Leave
        If Me.cbxTKNo.Text.Trim <> "" Then
            Dim myTable As DataTable = Me.cbxTKNo.DataSource
            For Each row As DataRow In myTable.Rows
                If row(0).ToString = Me.cbxTKNo.Text.Trim() Then
                    Me.cbxTKNo.SelectedValue = Me.cbxTKNo.Text.Trim()
                    Exit For
                End If
            Next
        End If
        If Me.cbxTKNo.Text.Trim = "" Then
            Me.txtTenTKNo.Text = ""
        End If
    End Sub

    Private Sub cbxTKNo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTKNo.SelectedIndexChanged
        If Me.cbxTKNo.SelectedIndex >= 0 Then
            Dim myTable As DataTable = Me.cbxTKNo.DataSource
            Me.txtTenTKNo.Text = myTable.Rows(Me.cbxTKNo.SelectedIndex)(1).ToString
        Else
            Me.txtTenTKNo.Text = ""
        End If
    End Sub

    Private Sub cbxTKCo_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTKCo.Leave
        If Me.cbxTKCo.Text.Trim <> "" Then
            Dim myTable As DataTable = Me.cbxTKCo.DataSource
            For Each row As DataRow In myTable.Rows
                If row(0).ToString = Me.cbxTKCo.Text.Trim() Then
                    Me.cbxTKCo.SelectedValue = Me.cbxTKCo.Text.Trim()
                    Exit For
                End If
            Next
        End If
        If Me.cbxTKCo.Text.Trim = "" Then
            Me.txtTenTKCo.Text = ""
        End If
    End Sub

    Private Sub cbxTKCo_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTKCo.SelectedIndexChanged
        If Me.cbxTKCo.SelectedIndex >= 0 Then
            Dim myTable As DataTable = Me.cbxTKCo.DataSource
            Me.txtTenTKCo.Text = myTable.Rows(Me.cbxTKCo.SelectedIndex)(1).ToString
        Else
            Me.txtTenTKCo.Text = ""
        End If
    End Sub

    Private Sub cbxTKThue_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTKThue.Leave
        If Me.cbxTKThue.Text.Trim <> "" Then
            Dim myTable As DataTable = Me.cbxTKThue.DataSource
            For Each row As DataRow In myTable.Rows
                If row(0).ToString = Me.cbxTKThue.Text.Trim() Then
                    Me.cbxTKThue.SelectedValue = Me.cbxTKThue.Text.Trim()
                    Exit For
                End If
            Next
        End If
        If Me.cbxTKThue.Text.Trim = "" Then
            Me.txtTenTKThue.Text = ""
        End If
    End Sub

    Private Sub cbxTKThue_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxTKThue.SelectedIndexChanged
        If Me.cbxTKThue.SelectedIndex >= 0 Then
            Dim myTable As DataTable = Me.cbxTKThue.DataSource
            Me.txtTenTKThue.Text = myTable.Rows(Me.cbxTKThue.SelectedIndex)(1).ToString
        Else
            Me.txtTenTKThue.Text = ""
        End If
    End Sub

    Private Sub cbxKhachHang_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxKhachHang.SelectedIndexChanged
        If Me.cbxKhachHang.SelectedIndex >= 0 Then
            Dim myTable As DataTable = Me.cbxKhachHang.DataSource
            Me.txtTenKH.Text = myTable.Rows(Me.cbxKhachHang.SelectedIndex)(0).ToString
        Else
            Me.txtTenKH.Text = ""
        End If
    End Sub

    Private Sub cbxHangHoa_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxHangHoa.Leave
        If Me.cbxHangHoa.Text.Trim <> "" Then
            Dim myTable As DataTable = Me.cbxHangHoa.DataSource
            For Each row As DataRow In myTable.Rows
                If row(0).ToString = Me.cbxHangHoa.Text.Trim() Then
                    Me.cbxHangHoa.SelectedValue = Me.cbxHangHoa.Text.Trim()
                    Exit For
                End If
            Next
        End If
        If Me.cbxHangHoa.Text.Trim = "" Then
            Me.txtDVT.Text = ""
        End If
    End Sub

    Private Sub cbxHangHoa_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbxHangHoa.SelectedIndexChanged
        If Me.cbxHangHoa.SelectedIndex >= 0 Then
            Dim myTable As DataTable = Me.cbxHangHoa.DataSource
            Me.txtDVT.Text = myTable.Rows(Me.cbxHangHoa.SelectedIndex)(2).ToString
        Else
            Me.txtDVT.Text = ""
        End If
    End Sub

    Private Sub updateComboTaiKhoan()
        Dim mytable As DataTable = TruyVanDanhSach("select * from TaiKhoan")
        Me.cbxTKCo.DataSource = mytable
        Me.cbxTKCo.DisplayMember = "MaTK"
        Me.cbxTKCo.ValueMember = "MaTK"
        Me.cbxTKCo.SelectedIndex = -1
        Me.cbxTKNo.DataSource = mytable.Copy()
        Me.cbxTKNo.DisplayMember = "MaTK"
        Me.cbxTKNo.ValueMember = "MaTK"
        Me.cbxTKNo.SelectedIndex = -1
        Me.cbxTKThue.DataSource = mytable.Copy()
        Me.cbxTKThue.DisplayMember = "MaTK"
        Me.cbxTKThue.ValueMember = "MaTK"
        Me.cbxTKThue.SelectedIndex = -1
    End Sub

    Private Sub updateComboKhachHang()
        Me.cbxKhachHang.DataSource = TruyVanDanhSach("select * from KhachHang")
        Me.cbxKhachHang.DisplayMember = "TenKH"
        Me.cbxKhachHang.ValueMember = "MaKH"
        Me.cbxKhachHang.SelectedIndex = -1
    End Sub

    Private Sub updateComboHangHoa()
        Dim myTable As DataTable = TruyVanDanhSach("select hh.MaHH, hh.TenHH, dv.TenDV from HangHoa hh Left Join DonVi dv on hh.MaDV = dv.MaDV")
        Me.cbxHangHoa.DataSource = myTable
        Me.cbxHangHoa.DisplayMember = "TenHH"
        Me.cbxHangHoa.ValueMember = "MaHH"
        Me.cbxHangHoa.SelectedIndex = -1
    End Sub

    Function tinhTienHang(ByVal soluong As Object, ByVal dongia As Object) As Double
        Dim result As Double
        If Not soluong Is DBNull.Value AndAlso Not dongia Is DBNull.Value AndAlso Not soluong Is Nothing AndAlso Not dongia Is Nothing Then
            If IsNumeric(soluong) AndAlso IsNumeric(dongia) Then
                result = CDbl(soluong) * CDbl(dongia)
            End If
        End If
        Return result
    End Function

    Function tinhThanhTien(ByVal tienhang As Object, ByVal tienthue As Object) As Double
        Dim result As Double
        If Not tienhang Is DBNull.Value And Not tienhang Is Nothing AndAlso tienhang <> "" Then
            result += CDbl(tienhang)
        End If
        If Not tienthue Is DBNull.Value And Not tienthue Is Nothing AndAlso tienthue <> "" Then
            result += CDbl(tienthue)
        End If
        Return result
    End Function

    Function tinhTienThue(ByVal thuesuat As Object, ByVal tienhang As Object) As Double
        Dim result As Double
        If Not thuesuat Is DBNull.Value AndAlso Not tienhang Is DBNull.Value And Not thuesuat Is Nothing AndAlso Not tienhang Is Nothing Then
            If IsNumeric(thuesuat.Trim) AndAlso IsNumeric(tienhang.Trim) Then
                result = CDbl(tienhang) * CDbl(thuesuat) / 100
            End If
        End If
        Return result
    End Function

    Private Sub dgvList_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvList.KeyDown
        If Not dgvList.CurrentRow Is Nothing And e.KeyCode = Keys.Delete And txtSoCT.ReadOnly = False AndAlso MsgBox("Xóa chi tiết hàng hóa?", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            XoaCT()
        End If
    End Sub

    Private Function checkTextboxNumber(ByVal control As TextBox, Optional ByVal message As String = "Nhập liệu không chính xác. Vui lòng nhập kiểu số") As Boolean
        control.Text = control.Text.Trim()
        If control.Text <> "" Then
            If Not IsNumeric(control.Text) Then
                MsgBox(message)
                control.Focus()
                Return False
            End If
        End If
        Return True
    End Function

    Private Function checkTextboxNumberRequired(ByVal control As TextBox, Optional ByVal message As String = "") As Boolean
        control.Text = control.Text.Trim()
        If control.Text = "" Then
            MsgBox(message & " không thể trống.")
            control.Focus()
            Return False
        End If
        If Not IsNumeric(control.Text) Then
            MsgBox(message & " không chính xác. Xin vui lòng nhập kiểu số.")
            control.Focus()
            Return False
        End If
        Return True
    End Function

    Private Sub txtSL_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSL.Leave
        If checkTextboxNumber(txtSL) Then
            txtThanhTien.Text = tinhTienHang(txtSL.Text, txtDonGia.Text).ToString("#,###")
        End If
    End Sub

    Private Sub txtDonGia_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDonGia.Leave
        If checkTextboxNumber(txtDonGia) Then
            txtThanhTien.Text = tinhTienHang(txtSL.Text, txtDonGia.Text).ToString("#,###")
        End If
    End Sub

    Private Sub txtThueSuat_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtThueSuat.Leave
        If checkTextboxNumber(txtThueSuat) Then
            txtTienThue.Text = tinhTienThue(txtThueSuat.Text, txtThanhTien.Text).ToString("#,###")
            txtTongTienHang.Text = tinhThanhTien(txtThanhTien.Text, txtTienThue.Text).ToString("#,###")
        End If
    End Sub

    Private Sub txtThanhTien_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtThanhTien.TextChanged
        txtTienThue.Text = tinhTienThue(txtThueSuat.Text, txtThanhTien.Text).ToString("#,###")
        txtTongTienHang.Text = tinhThanhTien(txtThanhTien.Text, txtTienThue.Text).ToString("#,###")
    End Sub

    Private Sub txtTienThue_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTienThue.Leave
        If checkTextboxNumber(txtTienThue) Then
            txtTongTienHang.Text = tinhThanhTien(txtThanhTien.Text, txtTienThue.Text).ToString("#,###")
        End If
    End Sub

    Private Function checkValidDetail() As Boolean
        If Me.cbxHangHoa.SelectedValue Is Nothing Then
            MsgBox("Vui lòng chọn hàng hóa cần nhập.")
            Me.cbxHangHoa.Focus()
            Return False
        End If
        If themmoict Then
            For Each row As DataRow In dtsData.Tables(0).Rows
                If row(2) = cbxHangHoa.SelectedValue Then
                    MsgBox("Hàng hóa đã tồn tại trong danh sách")
                    cbxHangHoa.Focus()
                    Return False
                End If
            Next
        End If
        If Not checkTextboxNumberRequired(txtSL, "Số lượng") Then
            Return False
        End If
        If CDbl(txtSL.Text) > TinhSoLuongTonKho(strMaPhieu, cbxHangHoa.SelectedValue, dtpNgayCT.Value) Then
            MsgBox("Số lượng hàng tồn kho không đủ!")
            txtSL.Focus()
            Return False
        End If
        If Not checkTextboxNumberRequired(txtDonGia, "Đơn giá") Then
            Return False
        End If
        Return True
    End Function

    Private Sub clearDetail()
        themmoict = True
        Me.cbxHangHoa.SelectedIndex = -1
        Me.txtSL.Text = ""
        Me.txtDonGia.Text = ""
        Me.txtThanhTien.Text = ""
        Me.txtThueSuat.Text = ""
        Me.txtTienThue.Text = ""
        Me.txtTongTienHang.Text = ""
        lblTrangThai.Text = "thêm mới"
    End Sub

    Private Function getMaPhieu() As Integer
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        myConn.Open()
        Dim nextMaPhieu As Integer = 1
        Dim strSql As String = "select max(MaPhieu) from SoCai"
        Dim myComm As New SqlCommand(strSql, myConn)
        Dim result = myComm.ExecuteScalar()
        If Not result Is DBNull.Value Then
            nextMaPhieu = result + 1
        End If
        Return nextMaPhieu
    End Function

    Private Function getMaPhieuCT(ByVal con As SqlConnection) As Integer
        Dim nextMaPhieu As Integer = 1
        Dim strSql As String = "select max(MaPhieuCT) from CTSoCai"
        Dim myComm As New SqlCommand(strSql, con)
        Dim result = myComm.ExecuteScalar()
        If Not result Is DBNull.Value Then
            nextMaPhieu = result + 1
        End If
        Return nextMaPhieu
    End Function

    Private Function CapNhat() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myComm As New SqlCommand
        Dim result As Boolean = True
        myConn.Open()
        Dim transaction As SqlTransaction = myConn.BeginTransaction()
        Try

            myComm.Connection = myConn
            myComm.Transaction = transaction
            If strMaPhieu = "-1" Then
                strMaPhieu = ""
            End If
            Dim tmpMaPhieu As Integer
            If strMaPhieu <> "" Then
                tmpMaPhieu = strMaPhieu
                strSql = "Update SoCai set SoCT = @SoCT, SoHD = @SoHD, NgayHD = convert(datetime, @NgayHD, 103), NgayCT = convert(datetime, @NgayCT, 103), TKThue = @TKThue, "
                strSql &= " MaTK_No = @MaTK_No, MaTK_Co = @MaTK_Co, MaKH = @MaKH, GhiChu = @GhiChu, TongTien = @TongTien, TongThue = @TongThue, TruocThue = @TruocThue where MaPhieu = @MaPhieu"
            Else
                tmpMaPhieu = getMaPhieu()
                strSql = "Insert into SoCai(MaPhieu, LoaiCT, SoCT, SoHD, NgayHD, NgayCT, MaTK_No, MaTK_Co, MaKH, GhiChu, TKThue, TongTien, TongThue, TruocThue) "
                strSql &= " values (@MaPhieu, 'PXK', @SoCT, @SoHD, convert(datetime, @NgayHD, 103), convert(datetime, @NgayCT, 103), @MaTK_No, @MaTK_Co, @MaKH, @GhiChu, @TKThue, @TongTien, @TongThue, @TruocThue)"
            End If
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaPhieu", tmpMaPhieu)
            myComm.Parameters.AddWithValue("@SoCT", txtSoCT.Text)
            myComm.Parameters.AddWithValue("@SoHD", txtSoHD.Text)
            myComm.Parameters.AddWithValue("@NgayHD", dtpNgayHD.Value.ToString("dd/MM/yyyy"))
            myComm.Parameters.AddWithValue("@NgayCT", dtpNgayCT.Value.ToString("dd/MM/yyyy"))
            myComm.Parameters.AddWithValue("@MaTK_No", cbxTKNo.SelectedValue)
            myComm.Parameters.AddWithValue("@MaTK_Co", cbxTKCo.SelectedValue)
            myComm.Parameters.AddWithValue("@MaKH", cbxKhachHang.SelectedValue)
            myComm.Parameters.AddWithValue("@TKThue", cbxTKThue.SelectedValue)
            myComm.Parameters.AddWithValue("@GhiChu", txtDienGiai.Text)
            myComm.Parameters.AddWithValue("@TongTien", convertNumber(txtTongTien.Text))
            myComm.Parameters.AddWithValue("@TongThue", convertNumber(txtTongThue.Text))
            myComm.Parameters.AddWithValue("@TruocThue", convertNumber(txtTruocThue.Text))
            myComm.ExecuteNonQuery()
            strMaPhieu = tmpMaPhieu
            XoaChiTiet()
            CapNhatChiTiet(strMaPhieu)
            transaction.Commit()
            Dim aryTemp As New ArrayList
            For i As Integer = 0 To aryDelRow.Count - 1
                Dim row As Object() = aryDelRow.Item(i)
                CapNhatGiaVonHangBan(row(2), dtpNgayCT.Value.Month, dtpNgayCT.Value.Year)
                aryTemp.Add(row(2))
            Next
            Dim index As Integer = 0
            For Each row As DataRow In dtsData.Tables(0).Rows
                If aryTemp.IndexOf(row(2)) = -1 Then
                    CapNhatGiaVonHangBan(row(2), dtpNgayCT.Value.Month, dtpNgayCT.Value.Year)
                    aryTemp.Add(row(2))
                End If
                If Not themmoi Then
                    If row(2) <> dgvList.Rows(index).Tag And aryTemp.IndexOf(dgvList.Rows(index).Tag) = -1 Then
                        CapNhatGiaVonHangBan(dgvList.Rows(index).Tag, dtpNgayCT.Value.Month, dtpNgayCT.Value.Year)
                        aryTemp.Add(dgvList.Rows(index).Tag)
                    End If
                End If
                index += 1
            Next
        Catch ex As Exception
            transaction.Rollback()
            result = False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Sub CapNhatChiTiet(ByVal maphieu As Integer)
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myComm As New SqlCommand
        Dim strSql As String = ""
        myConn.Open()
        myComm.Connection = myConn
        For Each row As DataRow In dtsData.Tables(0).Rows
            myComm.Parameters.Clear()
            Dim MaPhieuCT As Integer
            If row(0) <> "" Then
                MaPhieuCT = row(0)
                strSql = "Update CTSoCai set MaHH = @MaHH, SoLuong = @SoLuong, DonGia = @DonGia, USD = @USD, ThueSuat = @ThueSuat,  "
                strSql &= " ThueNK = @ThueNK, TienThue = @TienThue, TonKho = 0, GiaVon = 0 where MaPhieuCT = @MaPhieuCT"
            Else
                MaPhieuCT = getMaPhieuCT(myComm.Connection)
                strSql = "Insert into CTSoCai(MaPhieuCT, MaPhieu, MaHH, SoLuong, DonGia, USD, ThueSuat, ThueNK, TienThue, TonKho, GiaVon) "
                strSql &= " values (@MaPhieuCT, @MaPhieu, @MaHH, @SoLuong, @DonGia, @USD, @ThueSuat, @ThueNK, @TienThue, 0, 0)"
                myComm.Parameters.AddWithValue("@MaPhieu", maphieu)
            End If
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaPhieuCT", MaPhieuCT)
            myComm.Parameters.AddWithValue("@MaHH", row(2))
            myComm.Parameters.AddWithValue("@SoLuong", convertNumber(row(3)))
            myComm.Parameters.AddWithValue("@DonGia", convertNumber(row(5)))
            myComm.Parameters.AddWithValue("@USD", False)
            myComm.Parameters.AddWithValue("@ThueSuat", convertNumber(row(7)))
            myComm.Parameters.AddWithValue("@TienThue", convertNumber(row(8)))
            myComm.Parameters.AddWithValue("@ThueNK", 0)
            myComm.ExecuteNonQuery()
            row(0) = MaPhieuCT
        Next
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Sub XoaChiTiet()
        If aryDelRow.Count > 0 Then
            Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
            Dim myComm As New SqlCommand
            myConn.Open()
            myComm.Connection = myConn
            Dim strSql As String = "Delete from CTSoCai where MaPhieuCT = @MaPhieuCT"
            myComm.CommandText = strSql
            For i As Integer = 0 To aryDelRow.Count - 1
                myComm.Parameters.Clear()
                Dim row As Object() = aryDelRow.Item(i)
                myComm.Parameters.AddWithValue("@MaPhieuCT", row(0))
                myComm.ExecuteNonQuery()
            Next
            myConn.Close()
            myConn.Dispose()
        End If
    End Sub

    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click
        ThemMoiDuLieu()
    End Sub

    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        If strMaPhieu <> "" And MsgBox("Xóa bút toán này?", MsgBoxStyle.OkCancel) = MsgBoxResult.Ok Then
            Dim success As Boolean = False
            Dim strSql As String = "Delete from CTSoCai where MaPhieu = @MaPhieu;Delete from SoCai where MaPhieu = @MaPhieu"
            Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
            Dim myComm As New SqlCommand
            Dim result As Boolean = True
            Try
                myConn.Open()
                myComm.Connection = myConn
                myComm.CommandText = strSql
                myComm.Parameters.AddWithValue("@MaPhieu", strMaPhieu)
                myComm.ExecuteNonQuery()
                success = True
            Catch ex As Exception

            End Try
            myConn.Close()
            myConn.Dispose()
            If success Then
                For Each row As DataRow In dtsData.Tables(0).Rows
                    CapNhatGiaVonHangBan(row(2), dtpNgayCT.Value.Month, dtpNgayCT.Value.Year)
                Next
                DocDuLieu(strMaPhieu, 4)
            End If
        End If
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        txtSoCT.ReadOnly = False
        txtSoHD.ReadOnly = False
        txtDienGiai.ReadOnly = False
        cbxTKCo.Enabled = True
        cbxTKNo.Enabled = True
        cbxTKThue.Enabled = True
        cbxKhachHang.Enabled = True
        cbxHangHoa.Enabled = True
        txtSL.ReadOnly = False
        txtDonGia.ReadOnly = False
        txtThueSuat.ReadOnly = False
        txtTienThue.ReadOnly = False
        btnCapNhat.Enabled = True

        btnLuu.Enabled = True
        btnTruoc.Enabled = False
        btnSau.Enabled = False
        btnDauTien.Enabled = False
        btnCuoiCung.Enabled = False
        themmoi = False
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Private Sub btnDauTien_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDauTien.Click
        DocDuLieu(strMaPhieu, 1)
    End Sub

    Private Sub btnTruoc_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTruoc.Click
        DocDuLieu(strMaPhieu, 2)
    End Sub

    Private Sub btnSau_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSau.Click
        DocDuLieu(strMaPhieu, 3)
    End Sub

    Private Sub btnCuoiCung_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCuoiCung.Click
        DocDuLieu(strMaPhieu, 4)
    End Sub

    Private Sub btnKhongLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKhongLuu.Click
        HuyBo()
        If strMaPhieu = -1 Then
            DocDuLieu(strMaPhieu, 4)
        Else
            DocDuLieu(strMaPhieu)
        End If
    End Sub

    Private Sub HuyBo()
        txtSoCT.ReadOnly = True
        txtSoHD.ReadOnly = True
        dtpNgayCT.Enabled = False
        dtpNgayHD.Enabled = False
        txtDienGiai.ReadOnly = True
        cbxTKCo.Enabled = False
        cbxTKNo.Enabled = False
        cbxTKThue.Enabled = False
        cbxKhachHang.Enabled = False
        cbxHangHoa.Enabled = False
        txtSL.ReadOnly = True
        txtDonGia.ReadOnly = True
        txtThueSuat.ReadOnly = True
        txtTienThue.ReadOnly = True
        btnCapNhat.Enabled = False

        btnXoa.Enabled = True
        btnTruoc.Enabled = True
        btnSau.Enabled = True
        btnDauTien.Enabled = True
        btnCuoiCung.Enabled = True

        btnLuu.Enabled = False
        btnSua.Enabled = True
        btnIn.Enabled = True
    End Sub

    Private Sub btnLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLuu.Click
        If validateInput() Then
            If CapNhat() Then
                btnXoa.Enabled = True
                MsgBox("Lưu thành công")
                HuyBo()
            Else
                MsgBox("Lưu thất bại")
            End If
        End If
    End Sub

    Private Function validateInput() As Boolean
        If Me.txtSoCT.Text.Trim = "" Then
            MsgBox("Số chứng từ không thể rỗng.", MsgBoxStyle.Information)
            txtSoCT.Focus()
            Return False
        End If
        If Not NgayChungTuHopLe(dtpNgayCT.Value) Then
            MsgBox("Ngày chứng từ không hợp lệ." & vbNewLine & "Tháng " & dtpNgayCT.Value.ToString("MM/yyyy") & " đã khóa sổ. Không thể chỉnh sửa!", MsgBoxStyle.Information)
            dtpNgayCT.Focus()
            Return False
        End If
        If cbxTKNo.SelectedValue Is Nothing Then
            MsgBox("Xin vui lòng chọn tài khoản nợ.", MsgBoxStyle.Information)
            cbxTKNo.Focus()
            Return False
        End If
        If cbxTKCo.SelectedValue Is Nothing Then
            MsgBox("Xin vui lòng chọn tài khoản có.", MsgBoxStyle.Information)
            cbxTKCo.Focus()
            Return False
        End If
        If cbxTKThue.SelectedValue Is Nothing Then
            MsgBox("Xin vui lòng chọn tài khoản thuế.", MsgBoxStyle.Information)
            cbxTKThue.Focus()
            Return False
        End If
        If cbxKhachHang.SelectedValue Is Nothing Then
            MsgBox("Xin vui lòng chọn khách hàng.", MsgBoxStyle.Information)
            cbxKhachHang.Focus()
            Return False
        End If
        If dtsData.Tables(0).Rows.Count = 0 Then
            MsgBox("Xin vui thêm hàng hóa.", MsgBoxStyle.Information)
            cbxHangHoa.Focus()
            Return False
        End If
        Return True
    End Function

    Private Sub btnCapNhat_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCapNhat.Click
        If checkValidDetail() Then
            If themmoict Then
                Me.cbxHangHoa.Focus()
                Dim row As DataRow = dtsData.Tables(0).NewRow
                row(0) = ""
                row(1) = dtsData.Tables(0).Rows.Count + 1
                row(2) = Me.cbxHangHoa.SelectedValue
                row(3) = Me.txtSL.Text
                row(4) = Me.txtDVT.Text
                row(5) = Me.txtDonGia.Text
                row(6) = Me.txtThanhTien.Text
                row(7) = Me.txtThueSuat.Text
                row(8) = Me.txtTienThue.Text
                row(9) = Me.txtTongTienHang.Text
                row(10) = cbxHangHoa.Text
                row(3) = formatNumber(row(3))
                row(5) = formatNumber(row(5))
                row(6) = formatNumber(row(6))
                row(7) = formatNumber(row(7))
                row(8) = formatNumber(row(8))
                row(9) = formatNumber(row(9))
                dtsData.Tables(0).Rows.Add(row)
                clearDetail()
            Else
                Me.cbxHangHoa.Focus()
                Dim row As DataRow = dtsData.Tables(0).Rows(dgvList.CurrentRow.Index)
                row(2) = Me.cbxHangHoa.SelectedValue
                row(3) = Me.txtSL.Text
                row(4) = Me.txtDVT.Text
                row(5) = Me.txtDonGia.Text
                row(6) = Me.txtThanhTien.Text
                row(7) = Me.txtThueSuat.Text
                row(8) = Me.txtTienThue.Text
                row(9) = Me.txtTongTienHang.Text
                row(10) = cbxHangHoa.Text
                row(3) = formatNumber(row(3))
                row(5) = formatNumber(row(5))
                row(6) = formatNumber(row(6))
                row(7) = formatNumber(row(7))
                row(8) = formatNumber(row(8))
                row(9) = formatNumber(row(9))
                MsgBox("Cập nhật vào danh sách thành công.")
                clearDetail()
            End If
            Dim tongtien As Double = 0
            Dim truocthue As Double = 0
            Dim tongthue As Double = 0
            For Each row As DataRow In dtsData.Tables(0).Rows
                truocthue += CDbl(row(6))
                tongthue += convertNumber(row(8))
                tongtien += CDbl(row(9))
            Next
            txtTruocThue.Text = truocthue.ToString("#,###")
            txtTongThue.Text = tongthue.ToString("#,###")
            txtTongTien.Text = tongtien.ToString("#,###")
            themmoict = True
        End If
    End Sub

    Private Sub dgvList_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvList.CellClick
        Dim row As DataRow = dtsData.Tables(0).Rows(dgvList.SelectedRows(0).Index)
        row(3) = formatNumber(row(3))
        row(5) = formatNumber(row(5))
        row(6) = formatNumber(row(6))
        Me.cbxHangHoa.SelectedValue = row(2)
        Me.txtSL.Text = row(3)
        Me.txtDVT.Text = row(4)
        Me.txtDonGia.Text = row(5)
        Me.txtThanhTien.Text = row(6)
        Me.txtThueSuat.Text = row(7)
        Me.txtTienThue.Text = row(8)
        Me.txtTongTienHang.Text = row(9)
        lblTrangThai.Text = "chỉnh sửa dòng " & row(1)
        themmoict = False
    End Sub

    Private Sub btnHangHoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHangHoa.Click
        Dim frm As New frmThemHangHoa()
        frm.ShowDialog()
        If frm.thanhcong Then
            updateComboHangHoa()
        End If
    End Sub

    Private Sub btnKhachHang_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKhachHang.Click
        Dim frm As New frmThemKhachHang()
        frm.ShowDialog()
        If frm.thanhcong Then
            updateComboKhachHang()
        End If
    End Sub

    Sub ThemMoiDuLieu()
        txtSoCT.Text = ""
        txtSoHD.Text = ""
        dtpNgayCT.Value = Now
        dtpNgayHD.Value = Now
        txtDienGiai.Text = ""
        cbxTKCo.SelectedIndex = -1
        cbxTKNo.SelectedIndex = -1
        cbxTKThue.SelectedIndex = -1
        cbxKhachHang.SelectedIndex = -1
        cbxHangHoa.SelectedIndex = -1
        txtSL.Text = ""
        txtDonGia.Text = ""
        txtThanhTien.Text = ""
        txtTongTien.Text = ""
        txtTongThue.Text = ""
        txtTruocThue.Text = ""
        txtThueSuat.Text = ""
        txtTienThue.Text = ""
        txtTongTienHang.Text = ""

        txtSoCT.ReadOnly = False
        txtSoHD.ReadOnly = False
        dtpNgayCT.Enabled = True
        dtpNgayHD.Enabled = True
        txtDienGiai.ReadOnly = False
        cbxTKCo.Enabled = True
        cbxTKNo.Enabled = True
        cbxTKThue.Enabled = True
        cbxKhachHang.Enabled = True
        cbxHangHoa.Enabled = True
        txtSL.ReadOnly = False
        txtDonGia.ReadOnly = False
        txtThueSuat.ReadOnly = False
        txtTienThue.ReadOnly = False
        btnCapNhat.Enabled = True

        btnLuu.Enabled = True
        btnXoa.Enabled = False
        btnSua.Enabled = False
        btnIn.Enabled = False
        btnTruoc.Enabled = False
        btnSau.Enabled = False
        btnDauTien.Enabled = False
        btnCuoiCung.Enabled = False

        dtsData.Tables(0).Rows.Clear()
        lblTrangThai.Text = "thêm mới"
        themmoi = True
        themmoict = True
        strMaPhieu = -1
    End Sub

    Function formatNumber(ByVal value As String) As String
        If IsNumeric(value) Then
            value = CDbl(value).ToString("#,###")
        End If
        Return value
    End Function

    Function convertNumber(ByVal value As String) As Double
        If IsNumeric(value) Then
            value = CDbl(value)
        Else
            value = 0
        End If
        Return value
    End Function

    Sub XoaCT()
        Dim i As Integer = dgvList.CurrentRow.Index
        Dim delrow As DataRow = dtsData.Tables(0).Rows(i)
        If delrow(0) <> "" Then
            aryDelRow.Add(dtsData.Tables(0).Rows(i).ItemArray)
        End If
        dtsData.Tables(0).Rows.RemoveAt(i)
        Dim tongtien As Double = 0
        Dim tongthue As Double = 0
        Dim truocthue As Double = 0
        For index As Integer = 0 To dtsData.Tables(0).Rows.Count - 1
            Dim row As DataRow = dtsData.Tables(0).Rows(index)
            row(1) = index + 1
            truocthue += CDbl(row(6))
            tongthue += convertNumber(row(8))
            tongtien += CDbl(row(9))
        Next
        txtTruocThue.Text = truocthue.ToString("#,###")
        txtTongThue.Text = tongthue.ToString("#,###")
        txtTongTien.Text = tongtien.ToString("#,###")
        Me.cbxHangHoa.SelectedIndex = -1
        Me.txtSL.Text = ""
        Me.txtDVT.Text = ""
        Me.txtDonGia.Text = ""
        Me.txtThanhTien.Text = ""
        Me.txtThueSuat.Text = ""
        Me.txtTienThue.Text = ""
        Me.txtTongTienHang.Text = ""
        lblTrangThai.Text = "thêm mới"
        themmoict = True
    End Sub

    Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        If dtsData.Tables(0).Rows.Count = 0 Then
            Exit Sub
        End If
        Dim strPath = Application.StartupPath & "\templates\phieu_xuat_kho.xls"
        Dim strSPath = Application.StartupPath & "\temp\PhieuXuatKho_" & Now.ToString("ddMMyyyy_") & Now.Millisecond & ".xls"
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Try

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False

            xlWorkBook = xlApp.Workbooks.Open(strPath)
            xlWorkBook.SaveCopyAs(strSPath)
            xlWorkBook.Close()
            xlWorkBook = xlApp.Workbooks.Open(strSPath)
            xlWorkSheet = xlWorkBook.Worksheets("Luu")
            'edit the cell with new value
            xlWorkSheet.Cells(1, 1).value = aryConfig.Item("CFG01")
            xlWorkSheet.Cells(2, 1).value = xlWorkSheet.Cells(2, 1).value.ToString.Replace("{DiaChi}", aryConfig.Item("CFG02"))
            xlWorkSheet.Cells(3, 1).value = xlWorkSheet.Cells(3, 1).value.ToString.Replace("{MST}", aryConfig.Item("CFG04"))
            xlWorkSheet.Cells(4, 6).value = xlWorkSheet.Cells(4, 6).value.ToString.Replace("{SoCT}", txtSoCT.Text)
            Dim strNgayCT As String = xlWorkSheet.Cells(5, 2).value.ToString.Replace("{NgayCT}", dtpNgayCT.Value.Day)
            strNgayCT = strNgayCT.Replace("{ThangCT}", dtpNgayCT.Value.Month)
            strNgayCT = strNgayCT.Replace("{NamCT}", dtpNgayCT.Value.Year)
            xlWorkSheet.Cells(5, 2).value = strNgayCT
            Dim strNgayXuat As String = xlWorkSheet.Cells(17, 6).value.ToString.Replace("{NgayCT}", Now.Day)
            strNgayXuat = strNgayXuat.Replace("{ThangCT}", Now.Month)
            strNgayXuat = strNgayXuat.Replace("{NamCT}", Now.Year)
            xlWorkSheet.Cells(17, 6).value = strNgayXuat
            'xlWorkSheet.Cells(6, 6).value = cbxTKNo.Text
            'xlWorkSheet.Cells(7, 6).value = cbxTKCo.Text
            xlWorkSheet.Cells(8, 1).value = xlWorkSheet.Cells(8, 1).value.ToString.Replace("{KhachHang}", cbxKhachHang.Text)
            xlWorkSheet.Cells(9, 1).value = xlWorkSheet.Cells(9, 1).value.ToString.Replace("{SoHD}", txtSoHD.Text)
            xlWorkSheet.Cells(9, 1).value = xlWorkSheet.Cells(9, 1).value.ToString.Replace("{NgayHD}", dtpNgayHD.Value.Day)
            xlWorkSheet.Cells(9, 1).value = xlWorkSheet.Cells(9, 1).value.ToString.Replace("{ThangHD}", dtpNgayHD.Value.Month)
            xlWorkSheet.Cells(9, 1).value = xlWorkSheet.Cells(9, 1).value.ToString.Replace("{NamHD}", dtpNgayHD.Value.Year)
            xlWorkSheet.Cells(22, 1).value = aryConfig.Item("CFG09")
            xlWorkSheet.Cells(22, 6).value = aryConfig.Item("CFG08")
            Dim i As Integer
            Dim tongtien As Double = 0
            For i = 0 To dtsData.Tables(0).Rows.Count - 1
                Dim row As DataRow = dtsData.Tables(0).Rows(i)
                xlWorkSheet.Cells(14 + i, 1).value = row(1)
                Dim hhTable As DataTable = cbxHangHoa.DataSource
                For Each rowhh As DataRow In hhTable.Rows
                    If rowhh(0) = row(2) Then
                        xlWorkSheet.Cells(14 + i, 2).value = rowhh(1)
                        Exit For
                    End If
                Next
                xlWorkSheet.Cells(14 + i, 3).value = row(2)
                xlWorkSheet.Cells(14 + i, 4).value = row(4)
                xlWorkSheet.Cells(14 + i, 5).value = CDbl(row(3))
                xlWorkSheet.Cells(14 + i, 7).value = CDbl(row(6))
                tongtien += CDbl(row(6))
                If i <> dtsData.Tables(0).Rows.Count - 1 Then
                    xlWorkSheet.Rows(15 + i).Select()
                    xlApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
                    xlWorkSheet.Rows(14).Select()
                    xlApp.Selection.Copy()
                    xlWorkSheet.Rows(15 + i).Select()
                    xlApp.ActiveSheet.Paste()
                End If
            Next
            xlWorkSheet.Cells(15 + i - 1, 7).value = "=Sum(G14:G" & (15 + i - 2) & ")"
            xlWorkSheet.Cells(16 + i - 1, 1).value = xlWorkSheet.Cells(16 + i - 1, 1).value.ToString.Replace("{TongTienChu}", DocSo(tongtien))
            xlWorkBook.Save()
            xlWorkSheet.Cells(1, 1).Select()
            xlApp.Selection.Copy()
            xlApp.Visible = True
            xlWorkBook.Activate()
            xlApp.DisplayAlerts = True

        Catch ex As Exception

        End Try

    End Sub
End Class